<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width">
<meta name="theme-color" content="#222"><meta name="generator" content="Hexo 5.4.2">

  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">



<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css" integrity="sha256-HtsXJanqjKTc8vVQjO4YMhiqFoXkfBsjBWcX91T1jr8=" crossorigin="anonymous">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/animate.css/3.1.1/animate.min.css" integrity="sha256-PR7ttpcvz8qrF57fur/yAx1qXMFJeJFiA6pSzWi0OIE=" crossorigin="anonymous">

<script class="next-config" data-name="main" type="application/json">{"hostname":"xiaoqingming18.github.io","root":"/","images":"/images","scheme":"Muse","darkmode":false,"version":"8.15.1","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12},"copycode":{"enable":false,"style":null},"bookmark":{"enable":false,"color":"#222","save":"auto"},"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"stickytabs":false,"motion":{"enable":true,"async":false,"transition":{"menu_item":"fadeInDown","post_block":"fadeIn","post_header":"fadeInDown","post_body":"fadeInDown","coll_header":"fadeInLeft","sidebar":"fadeInUp"}},"prism":false,"i18n":{"placeholder":"搜索...","empty":"没有找到任何搜索结果：${query}","hits_time":"找到 ${hits} 个搜索结果（用时 ${time} 毫秒）","hits":"找到 ${hits} 个搜索结果"}}</script><script src="/js/config.js"></script>

    <meta name="description" content="视图介绍视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。 假设我们需要从学生表中查询选修了数据库原理这门课的学生，有以下SQL语句： 12345SELECT Student.Sno,Sname,SdeptFROM Student,SC,CourseWHERE Student.Sno &#x3D; SC.Sno	AND SC.Cno &#x3D; Course.Cno	AND Course.C">
<meta property="og:type" content="article">
<meta property="og:title" content="数据库原理：视图">
<meta property="og:url" content="http://xiaoqingming18.github.io/2023/05/03/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8E%9F%E7%90%86%EF%BC%9A%E8%A7%86%E5%9B%BE/index.html">
<meta property="og:site_name" content="鸣蜩十七">
<meta property="og:description" content="视图介绍视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。 假设我们需要从学生表中查询选修了数据库原理这门课的学生，有以下SQL语句： 12345SELECT Student.Sno,Sname,SdeptFROM Student,SC,CourseWHERE Student.Sno &#x3D; SC.Sno	AND SC.Cno &#x3D; Course.Cno	AND Course.C">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="http://xiaoqingming18.github.io/2023/05/03/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8E%9F%E7%90%86%EF%BC%9A%E8%A7%86%E5%9B%BE/视图1.png">
<meta property="og:image" content="http://xiaoqingming18.github.io/2023/05/03/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8E%9F%E7%90%86%EF%BC%9A%E8%A7%86%E5%9B%BE/视图2.png">
<meta property="og:image" content="http://xiaoqingming18.github.io/2023/05/03/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8E%9F%E7%90%86%EF%BC%9A%E8%A7%86%E5%9B%BE/视图3.png">
<meta property="og:image" content="http://xiaoqingming18.github.io/2023/05/03/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8E%9F%E7%90%86%EF%BC%9A%E8%A7%86%E5%9B%BE/视图4.png">
<meta property="article:published_time" content="2023-05-03T13:57:41.000Z">
<meta property="article:modified_time" content="2023-05-11T13:48:32.526Z">
<meta property="article:author" content="鸣蜩十七">
<meta property="article:tag" content="-数据库原理 -视图">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="http://xiaoqingming18.github.io/2023/05/03/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8E%9F%E7%90%86%EF%BC%9A%E8%A7%86%E5%9B%BE/视图1.png">


<link rel="canonical" href="http://xiaoqingming18.github.io/2023/05/03/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8E%9F%E7%90%86%EF%BC%9A%E8%A7%86%E5%9B%BE/">



<script class="next-config" data-name="page" type="application/json">{"sidebar":"","isHome":false,"isPost":true,"lang":"zh-CN","comments":true,"permalink":"http://xiaoqingming18.github.io/2023/05/03/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8E%9F%E7%90%86%EF%BC%9A%E8%A7%86%E5%9B%BE/","path":"2023/05/03/数据库原理：视图/","title":"数据库原理：视图"}</script>

<script class="next-config" data-name="calendar" type="application/json">""</script>
<title>数据库原理：视图 | 鸣蜩十七</title>
  








  <noscript>
    <link rel="stylesheet" href="/css/noscript.css">
  </noscript>

<style>.github-emoji { position: relative; display: inline-block; width: 1.2em; min-height: 1.2em; overflow: hidden; vertical-align: top; color: transparent; }  .github-emoji > span { position: relative; z-index: 10; }  .github-emoji img, .github-emoji .fancybox { margin: 0 !important; padding: 0 !important; border: none !important; outline: none !important; text-decoration: none !important; user-select: none !important; cursor: auto !important; }  .github-emoji img { height: 1.2em !important; width: 1.2em !important; position: absolute !important; left: 50% !important; top: 50% !important; transform: translate(-50%, -50%) !important; user-select: none !important; cursor: auto !important; } .github-emoji-fallback { color: inherit; } .github-emoji-fallback img { opacity: 0 !important; }</style>
</head>

<body itemscope itemtype="http://schema.org/WebPage" class="use-motion">
  <div class="headband"></div>

  <main class="main">
    <div class="column">
      <header class="header" itemscope itemtype="http://schema.org/WPHeader"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏" role="button">
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <i class="logo-line"></i>
      <p class="site-title">鸣蜩十七</p>
      <i class="logo-line"></i>
    </a>
      <p class="site-subtitle" itemprop="description">鸣蜩十七是和女朋友谈恋爱的日子</p>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger" aria-label="搜索" role="button">
    </div>
  </div>
</div>



<nav class="site-nav">
  <ul class="main-menu menu"><li class="menu-item menu-item-home"><a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a></li><li class="menu-item menu-item-about"><a href="/about/" rel="section"><i class="fa fa-user fa-fw"></i>关于</a></li><li class="menu-item menu-item-tags"><a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签</a></li><li class="menu-item menu-item-categories"><a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类</a></li><li class="menu-item menu-item-archives"><a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档</a></li><li class="menu-item menu-item-schedule"><a href="/schedule/" rel="section"><i class="fa fa-calendar fa-fw"></i>日程表</a></li><li class="menu-item menu-item-sitemap"><a href="/sitemap.xml" rel="section"><i class="fa fa-sitemap fa-fw"></i>站点地图</a></li><li class="menu-item menu-item-commonweal"><a href="/404/" rel="section"><i class="fa fa-heartbeat fa-fw"></i>公益 404</a></li>
  </ul>
</nav>




</header>
        
  
  <aside class="sidebar">

    <div class="sidebar-inner sidebar-nav-active sidebar-toc-active">
      <ul class="sidebar-nav">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <div class="sidebar-panel-container">
        <!--noindex-->
        <div class="post-toc-wrap sidebar-panel">
            <div class="post-toc animated"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#%E8%A7%86%E5%9B%BE%E4%BB%8B%E7%BB%8D"><span class="nav-text">视图介绍</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E4%B8%BA%E4%BB%80%E4%B9%88%E4%BD%BF%E7%94%A8%E8%A7%86%E5%9B%BE"><span class="nav-text">为什么使用视图</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E5%88%9B%E5%BB%BA%E8%A7%86%E5%9B%BE"><span class="nav-text">创建视图</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%BB%BA%E7%AB%8B%E5%9C%A8%E5%A4%9A%E4%B8%AA%E5%9F%BA%E6%9C%AC%E8%A1%A8%E4%B8%8A%E7%9A%84%E8%A7%86%E5%9B%BE"><span class="nav-text">建立在多个基本表上的视图</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E4%BD%95%E6%97%B6%E9%9C%80%E8%A6%81%E5%9C%A8%E8%A7%86%E5%9B%BE%E5%90%8D%E5%90%8E%E9%9D%A2%E6%8C%87%E5%AE%9A%E5%B1%9E%E6%80%A7%E5%90%8D"><span class="nav-text">何时需要在视图名后面指定属性名</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E4%BD%BF%E7%94%A8%E8%A7%86%E5%9B%BE"><span class="nav-text">使用视图</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%9C%A8%E4%B8%80%E4%B8%AA%E5%B7%B2%E6%9C%89%E7%9A%84%E8%A7%86%E5%9B%BE%E4%B8%8A%E5%BB%BA%E7%AB%8B%E5%8F%A6%E4%B8%80%E4%B8%AA%E8%A7%86%E5%9B%BE"><span class="nav-text">在一个已有的视图上建立另一个视图</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%B8%A6%E8%A1%A8%E8%BE%BE%E5%BC%8F%E7%9A%84%E8%A7%86%E5%9B%BE"><span class="nav-text">带表达式的视图</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%88%86%E7%BB%84%E8%A7%86%E5%9B%BE"><span class="nav-text">分组视图</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E5%88%A0%E9%99%A4%E8%A7%86%E5%9B%BE"><span class="nav-text">删除视图</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E6%9F%A5%E8%AF%A2%E8%A7%86%E5%9B%BE"><span class="nav-text">查询视图</span></a></li></ol></div>
        </div>
        <!--/noindex-->

        <div class="site-overview-wrap sidebar-panel">
          <div class="site-author animated" itemprop="author" itemscope itemtype="http://schema.org/Person">
  <p class="site-author-name" itemprop="name">鸣蜩十七</p>
  <div class="site-description" itemprop="description">记录我的学习、生活和恋爱日常</div>
</div>
<div class="site-state-wrap animated">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
        <a href="/archives/">
          <span class="site-state-item-count">43</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
          <a href="/categories/">
        <span class="site-state-item-count">8</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
          <a href="/tags/">
        <span class="site-state-item-count">37</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>

        </div>
      </div>
    </div>

    
  </aside>


    </div>

    <div class="main-inner post posts-expand">


  


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="http://xiaoqingming18.github.io/2023/05/03/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8E%9F%E7%90%86%EF%BC%9A%E8%A7%86%E5%9B%BE/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="鸣蜩十七">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="鸣蜩十七">
      <meta itemprop="description" content="记录我的学习、生活和恋爱日常">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="数据库原理：视图 | 鸣蜩十七">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          数据库原理：视图
        </h1>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2023-05-03 21:57:41" itemprop="dateCreated datePublished" datetime="2023-05-03T21:57:41+08:00">2023-05-03</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar-check"></i>
      </span>
      <span class="post-meta-item-text">更新于</span>
      <time title="修改时间：2023-05-11 21:48:32" itemprop="dateModified" datetime="2023-05-11T21:48:32+08:00">2023-05-11</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8E%9F%E7%90%86/" itemprop="url" rel="index"><span itemprop="name">-数据库原理</span></a>
        </span>
    </span>

  
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
        <h1 id="视图介绍"><a href="#视图介绍" class="headerlink" title="视图介绍"></a>视图介绍</h1><p>视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。</p>
<p>假设我们需要从学生表中查询选修了数据库原理这门课的学生，有以下SQL语句：</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> Student.Sno,Sname,Sdept</span><br><span class="line"><span class="keyword">FROM</span> Student,SC,Course</span><br><span class="line"><span class="keyword">WHERE</span> Student.Sno <span class="operator">=</span> SC.Sno</span><br><span class="line">	<span class="keyword">AND</span> SC.Cno <span class="operator">=</span> Course.Cno</span><br><span class="line">	<span class="keyword">AND</span> Course.Cname <span class="operator">=</span> <span class="string">'数据库原理'</span>;</span><br></pre></td></tr></tbody></table></figure>
<p>我们可以把上面的查询包装成一个名为<code>ChoiceDBStudents</code>的虚拟表（视图），则可以像下面这样轻松地检索出数据：</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> Sno,Sname,Sdept</span><br><span class="line"><span class="keyword">FROM</span> ChoiceDBStudents</span><br></pre></td></tr></tbody></table></figure>
<p>这就是视图的作用。<code>ChoiceDBStudents</code>是一个视图，不包含任何数据，包含的是一个查询。</p>
<h1 id="为什么使用视图"><a href="#为什么使用视图" class="headerlink" title="为什么使用视图"></a>为什么使用视图</h1><p>下面是使用视图的一些好处和常见应用：</p>
<ol>
<li>重用SQL语句。</li>
<li>简化复杂的SQL操作。</li>
<li>使用表的一部分。</li>
<li>保护数据，可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。</li>
<li>更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据。</li>
</ol>
<p>创建视图之后，<strong>可以像使用基本表一样使用视图</strong>，可以对视图执行<code>SELECT</code>操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据。</p>
<p>重要的是，视图本身并不包含数据，返回的数据是从其他表中检索出来的，再添加或更改这些表中的数据时，视图将返回改变过的数据。</p>
<h1 id="创建视图"><a href="#创建视图" class="headerlink" title="创建视图"></a>创建视图</h1><p>视图用<code>CREATE VIEW</code>语句来创建，格式如下：</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">VIEW</span> <span class="operator">&lt;</span>视图名<span class="operator">&gt;</span> [(<span class="operator">&lt;</span>列名<span class="operator">&gt;</span> [,<span class="operator">&lt;</span>列名<span class="operator">&gt;</span>]···)]</span><br><span class="line"><span class="keyword">AS</span> <span class="operator">&lt;</span>子查询<span class="operator">&gt;</span></span><br><span class="line">[<span class="keyword">WITH</span> <span class="keyword">CHECK</span> OPTION]</span><br></pre></td></tr></tbody></table></figure>
<p>其中，子查询可以是任意的<code>SELECT</code>语句，是否可以含有<code>ORDER BY</code>子句和<code>DISTINCT</code>短语取决于具体系统的实现。<code>WITH CHECK OPTION</code>表示对视图进行<code>UPDATE</code>、<code>INSERT</code>、和<code>DELETE</code>操作时要保证操作的行满足视图定义中的谓词条件，即符合<code>WHERE</code> 子句的条件。</p>
<p>看下面的例子，建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生：</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">VIEW</span> IS_Student</span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line"><span class="keyword">SELECT</span> Sno,Sname,Sage</span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">WHERE</span> Sdept <span class="operator">=</span> <span class="string">'信息系'</span></span><br><span class="line"><span class="keyword">WITH</span> <span class="keyword">CHECK</span> OPTION;</span><br></pre></td></tr></tbody></table></figure>
<h2 id="建立在多个基本表上的视图"><a href="#建立在多个基本表上的视图" class="headerlink" title="建立在多个基本表上的视图"></a>建立在多个基本表上的视图</h2><p>建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）：</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">VIEW</span> IS_S1</span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line"><span class="keyword">SELECT</span> Student.Sno,Sname,Grade</span><br><span class="line"><span class="keyword">FROM</span> Student,SC</span><br><span class="line"><span class="keyword">WHERE</span> Sdept <span class="operator">=</span> <span class="string">'信息系'</span></span><br><span class="line">	<span class="keyword">AND</span> Student.Sno <span class="operator">=</span> SC.Sno</span><br><span class="line">	<span class="keyword">AND</span> SC.Cno <span class="operator">=</span> <span class="string">'1'</span>;</span><br></pre></td></tr></tbody></table></figure>
<p>建立的视图如下：</p>
<p><img src="/2023/05/03/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8E%9F%E7%90%86%EF%BC%9A%E8%A7%86%E5%9B%BE/视图1.png" alt="信息系选修1号课程的学生视图"></p>
<p>上面的例子中，由于视图<code>IS_S1</code>的属性列中包含了与<code>Student</code>表和<code>SC</code>表的同名列<code>Sno</code>，所以必须在视图名后面明确说明视图的各个属性列名。</p>
<h2 id="何时需要在视图名后面指定属性名"><a href="#何时需要在视图名后面指定属性名" class="headerlink" title="何时需要在视图名后面指定属性名"></a>何时需要在视图名后面指定属性名</h2><p>在创建视图时，如果视图的列名和底层表的列名一样，则不需要在视图名后面明确说明视图的各个属性列名。这是因为，视图已经包含了底层表的列名和对应的数据，因此在查询视图时可以直接使用视图的列名。</p>
<p>但是，在以下情况下，可能需要在视图名后面明确说明视图的各个属性列名：</p>
<ol>
<li>当视图的列名与底层表的列名不一致时，需要为视图的每个列明确指定列名。在这种情况下，视图的列名可以是任意名称，只要在查询视图时使用相应的列名即可。</li>
<li>当视图的定义中使用了计算列或函数等，这些列可能没有直接对应的列名。在这种情况下，需要为视图的计算列或函数指定别名，以便在查询视图时使用。</li>
<li>当视图的定义中包含了多个表时，如果这些表中有相同的列名，那么在查询视图时，需要明确指定每个列所属的表名或别名，以避免产生歧义。</li>
</ol>
<h1 id="使用视图"><a href="#使用视图" class="headerlink" title="使用视图"></a>使用视图</h1><h2 id="在一个已有的视图上建立另一个视图"><a href="#在一个已有的视图上建立另一个视图" class="headerlink" title="在一个已有的视图上建立另一个视图"></a>在一个已有的视图上建立另一个视图</h2><p>建立信息系选修了1号课程且成绩在90分以上的学生的视图：</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">VIEW</span> IS_S2</span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line"><span class="keyword">SELECT</span> Sno,Sname,Grade</span><br><span class="line"><span class="keyword">FROM</span> IS_S1</span><br><span class="line"><span class="keyword">WHERE</span> Grade <span class="operator">&gt;=</span> <span class="number">90</span>;</span><br></pre></td></tr></tbody></table></figure>
<p>建立的视图如下；</p>
<p><img src="/2023/05/03/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8E%9F%E7%90%86%EF%BC%9A%E8%A7%86%E5%9B%BE/视图2.png" alt="信息系选修了1号课程且成绩在90分以上的学生的视图"></p>
<h2 id="带表达式的视图"><a href="#带表达式的视图" class="headerlink" title="带表达式的视图"></a>带表达式的视图</h2><p>定义基本表时，表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。由于视图中的数据也是不实际存储的，所以定义视图时可以根据应用的需要设置一些派生属性列。这些派生属性列由于在基本表中并不实际存在，也成它们为<strong>虚拟列</strong>。带虚拟列的视图被称为<strong>带表达式的视图</strong>。</p>
<p>看一个例子，定义一个反映学生出生年份的视图（假设今年为2023年）：</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">VIEW</span> BT_S(Sno,Sname,Sbirth)</span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line"><span class="keyword">SELECT</span> Sno,Sname,<span class="number">2023</span><span class="operator">-</span>Sage</span><br><span class="line"><span class="keyword">FROM</span> Student;</span><br></pre></td></tr></tbody></table></figure>
<p>建立的视图如下：</p>
<p><img src="/2023/05/03/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8E%9F%E7%90%86%EF%BC%9A%E8%A7%86%E5%9B%BE/视图3.png" alt="反映学生出生年份的视图"></p>
<h2 id="分组视图"><a href="#分组视图" class="headerlink" title="分组视图"></a>分组视图</h2><p>上面的视图<code>BT_S</code>是一个带表达式的视图。<strong>视图中的出生年份是通过计算得到的</strong>。还可以用带有聚集函数和<code>GROUP BY</code>子句的查询来定义视图，这种视图称为<strong>分组视图</strong>。</p>
<p>来看一个例子，将学生的学号及平均成绩定义为一个视图：</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">VIEW</span> S_G(Sno,Gavg)</span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line"><span class="keyword">SELECT</span> Sno,<span class="built_in">AVG</span>(Grade)</span><br><span class="line"><span class="keyword">FROM</span> SC</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> Sno;</span><br></pre></td></tr></tbody></table></figure>
<p>建立的视图如下：</p>
<p><img src="/2023/05/03/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8E%9F%E7%90%86%EF%BC%9A%E8%A7%86%E5%9B%BE/视图4.png" alt="学生的学号及平均成绩视图"></p>
<h1 id="删除视图"><a href="#删除视图" class="headerlink" title="删除视图"></a>删除视图</h1><p>删除视图用<code>DROP</code>语句，格式如下：</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">VIEW</span> <span class="operator">&lt;</span>视图名<span class="operator">&gt;</span> [CASCADE]</span><br></pre></td></tr></tbody></table></figure>
<p><strong>如果欲删除的视图还导出了其他视图，应使用<code>CASCADE</code>将其导出的视图一并删除</strong>。</p>
<p>如果欲删除的视图导出了其他视图，但删除时没用<code>CASCADE</code>，则会导致删除失败。</p>
<h1 id="查询视图"><a href="#查询视图" class="headerlink" title="查询视图"></a>查询视图</h1>
    </div>

    
    
    

    <footer class="post-footer">
          <div class="post-tags">
              <a href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8E%9F%E7%90%86-%E8%A7%86%E5%9B%BE/" rel="tag"># -数据库原理 -视图</a>
          </div>

        

          <div class="post-nav">
            <div class="post-nav-item">
                <a href="/2023/04/28/java%EF%BC%9A%E5%BC%82%E5%B8%B8%E7%B1%BB%E4%B8%8E%E6%8D%95%E8%8E%B7%E5%BC%82%E5%B8%B8/" rel="prev" title="Java：异常类与捕获异常">
                  <i class="fa fa-chevron-left"></i> Java：异常类与捕获异常
                </a>
            </div>
            <div class="post-nav-item">
                <a href="/2023/05/05/%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%BB%84%E6%88%90%E5%8E%9F%E7%90%86%EF%BC%9A%E6%8C%87%E4%BB%A4%E7%B3%BB%E7%BB%9F/" rel="next" title="计算机组成原理：指令系统">
                  计算机组成原理：指令系统 <i class="fa fa-chevron-right"></i>
                </a>
            </div>
          </div>
    </footer>
  </article>
</div>






</div>
  </main>

  <footer class="footer">
    <div class="footer-inner">


<div class="copyright">
  &copy; 
  <span itemprop="copyrightYear">2023</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">鸣蜩十七</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" rel="noopener" target="_blank">Hexo</a> & <a href="https://theme-next.js.org/muse/" rel="noopener" target="_blank">NexT.Muse</a> 强力驱动
  </div>

    </div>
  </footer>

  
  <div class="toggle sidebar-toggle" role="button">
    <span class="toggle-line"></span>
    <span class="toggle-line"></span>
    <span class="toggle-line"></span>
  </div>
  <div class="sidebar-dimmer"></div>
  <div class="back-to-top" role="button" aria-label="返回顶部">
    <i class="fa fa-arrow-up fa-lg"></i>
    <span>0%</span>
  </div>

<noscript>
  <div class="noscript-warning">Theme NexT works best with JavaScript enabled</div>
</noscript>


  
  <script src="https://cdnjs.cloudflare.com/ajax/libs/animejs/3.2.1/anime.min.js" integrity="sha256-XL2inqUJaslATFnHdJOi9GfQ60on8Wx1C2H8DYiN1xY=" crossorigin="anonymous"></script>
<script src="/js/comments.js"></script><script src="/js/utils.js"></script><script src="/js/motion.js"></script><script src="/js/schemes/muse.js"></script><script src="/js/next-boot.js"></script>

  




  




  

  <script class="next-config" data-name="enableMath" type="application/json">true</script><script class="next-config" data-name="mathjax" type="application/json">{"enable":true,"tags":"none","js":{"url":"https://cdnjs.cloudflare.com/ajax/libs/mathjax/3.2.2/es5/tex-mml-chtml.js","integrity":"sha256-MASABpB4tYktI2Oitl4t+78w/lyA+D7b/s9GEP0JOGI="}}</script>
<script src="/js/third-party/math/mathjax.js"></script>



</body>
</html>
